James E. Blair <jeblair@berkeley.edu>
Paul Fisher <pnfisher@berkeley.edu>
Copyright 2008 The Regents of the University of California.
CalMail is UC Berkeley's central campus email system.
Many email systems use LDAP to integrate with enterprise infrastructure.
We stored account information in LDAP beside our central campus identity system. Account information is:
Our LDAP system utilized a multi-master configuration for high availability.
LDAP was topoligically distant and subject to interference from load balancers, firewalls, etc.
Multiple masters got out of sync, a problem exacerbated by lack of transactions.
Queries had to be hand-optimized.
Complex searches to answer business questions no longer feasible.
Slow performance at 200-300 queries per second.
Outages took out the entire email system.
1) Buy new hardware.
2) Put the entire LDAP database in RAM.
Our requirements for account data:
We have 10 machines running Cyrus with 4 GiB of RAM.
They can host data nodes at a cost of only a few hundred MiB each.
Our MTA and webmail machines can run MySQL servers and each can handle its own query processing.
Data are stored in memory, but backed by disk. Nodes are divided into node groups for redundancy.
API nodes are typically MySQL servers. They process queries, fetch data from data nodes, and return data to clients.
Not required for continued operation of the cluster, management nodes are responsible for cluster configuration information, and help nodes join the cluster.
$ ndb_mgm -- NDB Cluster -- Management Client -- ndb_mgm> show Connected to Management Server at: 192.168.1.15:1186 Cluster Configuration --------------------- [ndbd(NDB)] 10 node(s) id=1 @192.168.3.1 (Version: 5.0.30, Nodegroup: 0) id=2 @192.168.3.2 (Version: 5.0.30, Nodegroup: 0) id=3 @192.168.3.3 (Version: 5.0.30, Nodegroup: 1) id=4 @192.168.3.4 (Version: 5.0.30, Nodegroup: 1, Master) id=5 @192.168.3.5 (Version: 5.0.30, Nodegroup: 2) id=6 @192.168.3.6 (Version: 5.0.30, Nodegroup: 2) id=7 @192.168.3.7 (Version: 5.0.30, Nodegroup: 3) id=8 @192.168.3.8 (Version: 5.0.30, Nodegroup: 3) id=9 @192.168.3.9 (Version: 5.0.30, Nodegroup: 4) id=10 @192.168.3.10 (Version: 5.0.30, Nodegroup: 4) [ndb_mgmd(MGM)] 11 node(s) id=41 @192.168.1.15 (Version: 5.0.30) id=42 @192.168.1.70 (Version: 5.0.30) id=43 (not connected, accepting connect from 192.168.1.20) id=44 (not connected, accepting connect from 192.168.1.65) id=45 (not connected, accepting connect from 192.168.1.75) id=46 (not connected, accepting connect from 192.168.1.85) id=51 (not connected, accepting connect from 192.168.2.20) id=52 (not connected, accepting connect from 192.168.2.22) id=53 (not connected, accepting connect from 192.168.2.24) id=57 (not connected, accepting connect from 192.168.1.93) id=59 (not connected, accepting connect from 192.168.1.80) [mysqld(API)] 15 node(s) id=21 @192.168.1.15 (Version: 5.0.30) id=22 @192.168.1.70 (Version: 5.0.30) id=23 @192.168.1.20 (Version: 5.0.30) id=24 @192.168.1.65 (Version: 5.0.30) id=25 @192.168.1.75 (Version: 5.0.30) id=26 @192.168.1.85 (Version: 5.0.30) id=31 @192.168.2.20 (Version: 5.0.30) id=32 @192.168.2.22 (Version: 5.0.30) id=33 @192.168.2.24 (Version: 5.0.30) id=34 @192.168.2.29 (Version: 5.0.30) id=37 @192.168.1.93 (Version: 5.0.30) id=39 @192.168.1.80 (Version: 5.0.30) id=61 @192.168.2.10 (Version: 5.0.30) id=62 @192.168.2.12 (Version: 5.0.30) id=63 @192.168.2.14 (Version: 5.0.30)
Every byte counts.
set ipn = inet_aton(in_ip_addr);
15 bytes vs 4 bytes.
Avoid blobs.
address varchar(255) NOT NULL,
Blobs cause a hidden auxiliary table to be created.
Avoid enums.
exp_state varchar(12) default NULL,
Changing enums means a schema update.
Exim is a free software MTA developed at University of Cambridge
Message Processing...
A string expansion language enables custom rules and procedures anywhere along the way.
MYSQL_ACCOUNT_QUERY = ${lookup mysql \
{select a.* from calmail.account a, \
calmail.domain d \
where \
a.domain_id=d.id and \
a.localpart='${quote_mysql:$local_part}' and \
d.name='${quote_mysql:$domain}' and \
a.state='active';}}
# ...The cyrus router...
cyrus:
verify = false
driver = manualroute
transport = cyrus_lmtp
route_data = ${extract{host}{MYSQL_ACCOUNT_QUERY}{$value}fail}
We implement greylisting (the Postgrey algorithm) using MySQL stored procedures.
GREYLIST_QUERY = ${lookup mysql{ \
select greylist_defer_p('GREYLIST_KEY','$sender_host_address', \
GREYLIST_AWL_COUNT, GREYLIST_DELAY, \
GREYLIST_TIMESTAMP_UPDATE)}{$value}{0}}
# ...The greylist ACL:
defer
condition = ${if def:ACL_GREYLIST {$ACL_GREYLIST}{0}}
!hosts = +our_network : \
net-cdb;CONF_DIR/greylist_whitelist_ips.cdb : \
net24-cdb;CONF_DIR/greylist_whitelist_ips.cdb : \
partial()cdb;CONF_DIR/greylist_whitelist_domains.cdb : \
nwildlsearch;CONF_DIR/greylist_whitelist_regexps
condition = GREYLIST_QUERY
message = Your message is subject to a greylisting policy.\n\
Please resend your message after five minutes have elapsed.\n\
Contact postmaster@berkeley.edu for more information.
We use TurboGears for our account management application.
@expose()
@validate(form=add_forward_form)
@identity.require(identity.not_anonymous())
def add_forward(self, add_forward=[]):
account = identity.current.user
for forward in add_forward:
if forward not in [x.target for x in account.forwards]:
account.addForwardingAddress(forward)
log("added forwarding address %s" % forward)
raise redirect('/account/forwarding')
account_table =
Table('account', metadata,
Column('domain_id', Integer, ForeignKey('domain.id'), nullable=False))
class Account(object): pass
assign_mapper(ctx, Account, account_table,
properties = dict(
transactions = relation(Transaction)
aliases = relation(AccountAlias)
domain = relation(Domain),
forwards = relation(AccountForward)))
We store mailing list subscription data for Mailman in the MySQL cluster database.
This helps scale Mailman to the 22,220 mailing lists and 1,164,015 subscribers we support.
mlist = MailList.MailList()
mlist.Create(listname, owner, password, language, host)
mlist.members = MMSQLSubscribers(listname, 0)
mlist.digest_members = MMSQLSubscribers(listname, 1)
class MMSQLSubscribers(object, DictMixin):
...
def __setstate__(self, d):
self.__dict__.update(d)
self.lst = List.by_listname(self.listname)
self.subscribers = self.lst.subscribers
def __getitem__(self, key):
return self.subscribers[key].address
What we dislike:
What we like: